// Copyright (c) Microsoft Corporation.  All rights reserved.

using System;
using System.Collections.Generic;
using System.Text;

using Data = PfRep.DataEntities;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Diagnostics;

namespace PfRep.DataAccess
{
    /// <summary>
    /// A class for SQL Server-related data access operations
    /// </summary>
    public static class SQL
    {
        // A cached Command object
        private static SqlCommand insertIndexedFolderData = null;

        /// <summary>
        /// Creates a new database connection to the SQL Server
        /// </summary>
        /// <returns></returns>
        public static SqlConnection CreateConnection()
        {
            // Retrieve the connectionstring
            string connectionString = Common.Config.GetConfigValue(Common.Config.PARAM_SQL_SERVER_CONNECTIONSTRING);
            Common.Diagnostics.Trace("CreateConnection", "SQL connection string retrieved: " + connectionString);

            // Open the connection
            SqlConnection connection = CreateConnection(connectionString);
            return connection;
        }

        /// <summary>
        /// Creates the PfRep database
        /// </summary>
        /// <param name="connectionString">Connection string to the master database</param>
        /// <param name="databaseName">The database's name going to be created</param>
        /// <param name="databaseScript">Script that creates database objects</param>
        public static void CreatePfRepDatabase(
            string connectionString,
            string databaseName,
            string databaseScript)
        {
            // Connect to master
            using (SqlConnection masterConnection = CreateConnection(connectionString))
            {
                Common.Diagnostics.Trace("DA.CreatePfRepDatabase", "Opened master db connection");

                // Creates the database
                new SqlCommand(
                    string.Format("CREATE DATABASE [{0}]", databaseName),
                    masterConnection).ExecuteNonQuery();
                Common.Diagnostics.Trace("DA.CreatePfRepDatabase", "Database created");

                // Log on to the new database
                masterConnection.ChangeDatabase(databaseName);
                Common.Diagnostics.Trace("DA.CreatePfRepDatabase", "Database context changed");
                
                // Break the SQL script to statements (delimited by the GO command).
                //  Without doing this, it doesn't work. NOTE: it's important that
                //  the SQL script shouldn't contain *ANY* GO word except the ones
                //  used to delimit, otherwise it won't work. We are using GO because
                //  OSQL also understands GO - so the script remaing compatible.
                //  Another solution would be to use a commented delimiter.
                string[] delimitedSqlCommand = databaseScript.Split(
                    new string[1] { "GO" }, StringSplitOptions.None);
                Common.Diagnostics.Trace("DA.CreatePfRepDatabase", "Command split succeeded");
                
                // Creates the db objects
                foreach (string sqlCommand in delimitedSqlCommand)
                {
                    Common.Diagnostics.Trace("DA.CreatePfRepDatabase", "Excuting command " 
                        + sqlCommand);
                    SqlCommand command = new SqlCommand(sqlCommand, masterConnection);
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                    Common.Diagnostics.Trace("DA.CreatePfRepDatabase", "Command executed succesfully");
                }
            }
        }

        /// <summary>
        /// Inserts a new index value
        /// </summary>
        /// <param name="folderId"></param>
        /// <param name="contentIndexId"></param>
        /// <param name="lastItemModification"></param>
        /// <param name="connection"></param>
        public static void InsertIndexedFolderData(
            int contentIndexId,
            Data.Folder folder,
            SqlConnection connection)
        {
            // HACK: If the date is older than 1900, we set it to MinValue
            if (folder.LastModified.Year < 1900)
                folder.LastModified = DateTime.MinValue;

            try
            {

                // Set up the command object if it's not yet created
                if (insertIndexedFolderData == null)
                {
                    insertIndexedFolderData =
                        new SqlCommand("InsertIndexedFolderData", connection);
                    insertIndexedFolderData.CommandType = CommandType.StoredProcedure;

                    // Add required parameters
                    insertIndexedFolderData.Parameters.Add(
                        CreateParameter("@folderId", SqlDbType.Int, 0));
                    insertIndexedFolderData.Parameters.Add(
                        CreateParameter("@contentIndexId", SqlDbType.Int, 0));

                    // Add optional parameters
                    insertIndexedFolderData.Parameters.Add(
                        CreateParameter("@lastItemModification", SqlDbType.DateTime,
                        DBNull.Value));

                    Common.Diagnostics.Trace("InsertIndexedFolderData", "SP created and parameterized");
                }

                // Parameterize it
                insertIndexedFolderData.Parameters[0].Value = folder.ID;
                insertIndexedFolderData.Parameters[1].Value = contentIndexId;
                if (folder.LastModified == DateTime.MinValue)
                    insertIndexedFolderData.Parameters[2].Value = DBNull.Value;
                else
                        insertIndexedFolderData.Parameters[2].Value = folder.LastModified;
                //Debug.WriteLine("InsertIndexedFolderData: " + insertIndexedFolderData.Parameters[0].Value + ", " + insertIndexedFolderData.Parameters[1].Value + ", " + insertIndexedFolderData.Parameters[2].Value + ", ");

                // Call the SP
                insertIndexedFolderData.ExecuteNonQuery();
                Common.Diagnostics.Trace("InsertIndexedFolderData", "SP ran successfully");
            }
            catch (Exception e)
            {
                PfRep.Common.Diagnostics.Trace("PfRepDataEntities.InsertIndexedFolderData", 
                    "ERROR: " + e.Message + " " + e.StackTrace);
            }
        }

        /// <summary>
        /// Gets all folders for a given folder tree
        /// </summary>
        /// <param name="folderTreeId"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        public static List<Data.Folder> GetAllFolders(
            int folderTreeId,
            SqlConnection connection)
        {
            // Set up the command object
            SqlCommand getAllFolders =
                new SqlCommand("GetAllFolders", connection);
            getAllFolders.CommandType = CommandType.StoredProcedure;

            // Add parameters
            getAllFolders.Parameters.Add(
                CreateParameter("@folderTreeId", SqlDbType.Int, folderTreeId));

            Common.Diagnostics.Trace("GetAllFolders", "SP created and parameterized");

            // Call the SP
            SqlDataReader results = getAllFolders.ExecuteReader();
            Common.Diagnostics.Trace("GetAllFolders", "SP ran successfully");

            // Get the column IDs
            int folderIdColumnId = results.GetOrdinal("FolderID");
            int folderNameColumnId = results.GetOrdinal("FolderName");
            int folderKeyColumnId = results.GetOrdinal("FolderKey");
            int ownerNameColumnId = results.GetOrdinal("OwnerName");
            int ownerEmailColumnId = results.GetOrdinal("OwnerEmail");
            int actionPlanIdColumnId = results.GetOrdinal("ActionPlanID");
            int actionPlanNameColumnId = results.GetOrdinal("ActionPlanName");
            int noteColumnId = results.GetOrdinal("Note");
            
            // Convert the results
            List<Data.Folder> retVal = new List<Data.Folder>();
            while (results.Read())
            {
                retVal.Add(new Data.Folder(
                    results.GetInt32(folderIdColumnId),
                    results.GetString(folderNameColumnId),
                    results.GetString(folderKeyColumnId),
                    false, 
                    0, 
                    DateTime.MinValue,
                    results.IsDBNull(ownerNameColumnId) ? "" : results.GetString(ownerNameColumnId),
                    results.IsDBNull(ownerEmailColumnId) ? "" : results.GetString(ownerEmailColumnId),
                    (Data.ActionPlan)results.GetInt32(actionPlanIdColumnId),
                    results.GetString(actionPlanNameColumnId),
                    results.GetString(noteColumnId)));
            }
            Common.Diagnostics.Trace("GetFolderTrees", "Data transformed");

            return retVal;
        }

        /// <summary>
        /// Gets the list of folder trees
        /// </summary>
        /// <param name="connection">SQL connection</param>
        /// <returns></returns>
        public static ArrayList GetFolderTrees(SqlConnection connection)
        {
            ArrayList retVal = new ArrayList();

            // Set up the command object
            SqlCommand getFolderTrees =
                new SqlCommand("GetFolderTrees", connection);
            getFolderTrees.CommandType = CommandType.StoredProcedure;
            Common.Diagnostics.Trace("GetFolderTrees", "SP created");

            // Call the SP
            SqlDataReader results = getFolderTrees.ExecuteReader();
            Common.Diagnostics.Trace("GetFolderTrees", "SP ran successfully");

            // Convert the results
            while (results.Read())
            {
                retVal.Add(new Data.FolderTree(
                    results.GetInt32(results.GetOrdinal("FolderTreeID")),
                    results.GetString(results.GetOrdinal("FolderTreeName")),
                    results.GetString(results.GetOrdinal("FolderTreeUrl"))));
            }
            Common.Diagnostics.Trace("GetFolderTrees", "Data transformed");

            return retVal;
        }

        /// <summary>
        /// Gets the list of action plans
        /// </summary>
        /// <param name="connection">SQL connection</param>
        /// <returns></returns>
        public static ArrayList GetActionPlans(SqlConnection connection)
        {
            ArrayList retVal = new ArrayList();

            // Set up the command object
            SqlCommand getActionPlans =
                new SqlCommand("GetActionPlans", connection);
            getActionPlans.CommandType = CommandType.StoredProcedure;
            Common.Diagnostics.Trace("GetActionPlans", "SP created");

            // Call the SP
            SqlDataReader results = getActionPlans.ExecuteReader();
            Common.Diagnostics.Trace("GetActionPlans", "SP ran successfully");

            // Convert the results
            while (results.Read())
            {
                retVal.Add(
                    new KeyValuePair<int, string>(
                    results.GetInt32(results.GetOrdinal("ActionPlanID")),
                    results.GetString(results.GetOrdinal("ActionPlanName"))));
            }
            Common.Diagnostics.Trace("GetActionPlans", "Data transformed");

            return retVal;
        }

        /// <summary>
        /// Gets the list of content statistics for a given folder tree
        /// </summary>
        /// <param name="folderTreeId">Unique SQL ID of the Folder Tree</param>
        /// <param name="connection">SQL connection</param>
        /// <returns></returns>
        public static ArrayList GetContentStatistics(
            int folderTreeId,
            SqlConnection connection)
        {
            ArrayList retVal = new ArrayList();

            // Set up the command object
            SqlCommand getContentStatistics =
                new SqlCommand("GetContentStatistics", connection);
            getContentStatistics.CommandType = CommandType.StoredProcedure;

            // Parameterize it
            getContentStatistics.Parameters.Add(CreateParameter("@folderTreeId", SqlDbType.Int, folderTreeId));
            Common.Diagnostics.Trace("GetContentStatistics", "SP created");

            // Call the SP
            SqlDataReader results = getContentStatistics.ExecuteReader();
            Common.Diagnostics.Trace("GetContentStatistics", "SP ran successfully");

            // Convert the results
            while (results.Read())
            {
                retVal.Add(new KeyValuePair<int, string>(
                    results.GetInt32(results.GetOrdinal("ContentIndexID")),
                    results.GetString(results.GetOrdinal("ContentIndexName"))));
            }
            Common.Diagnostics.Trace("GetContentStatistics", "Data transformed");

            return retVal;
        }

        
        /// <summary>
        /// Retrieves the number of folders from the consolidated database
        /// </summary>
        /// <param name="folderTreeId">Public folder tree ID</param>
        /// <param name="connection">SQL connection</param>
        /// <returns>Number of folders</returns>
        public static int GetNumberOfPublicFolders(
            int folderTreeId, 
            SqlConnection connection)
        {
            // Set up the command object
            SqlCommand getNumberOfPfs =
                new SqlCommand("GetNumberOfPublicFolders", connection);
            getNumberOfPfs.CommandType = CommandType.StoredProcedure;

            // Add parameters
            getNumberOfPfs.Parameters.Add(CreateParameter("@folderTreeId", SqlDbType.Int, folderTreeId));
            getNumberOfPfs.Parameters.Add(CreateReturnParameter("@numberOfPublicFolders", SqlDbType.Int));

            Common.Diagnostics.Trace("GetNumberOfPublicFolders", "SP created and parameterized");

            // Call the SP
            getNumberOfPfs.ExecuteNonQuery();
            Common.Diagnostics.Trace("GetNumberOfPublicFolders", "SP ran succesfully");

            // Retrieve out parameter
            int numberOfPfs = (int)getNumberOfPfs.Parameters[1].Value;
            Common.Diagnostics.Trace("GetNumberOfPublicFolders", "Retval retrieved");

            return numberOfPfs;
        }

        /// <summary>
        /// Inserts a new folder to the folder index
        /// </summary>
        /// <param name="folderIndexId"></param>
        /// <param name="folder"></param>
        /// <param name="parentFolderId"></param>
        /// <returns>The newly created folder's ID</returns>
        public static int InsertFolderToFolderIndex(
            int folderIndexId, 
            Data.Folder folder, 
            int? parentFolderId,
            SqlConnection connection)
        {
            // Set up the command object
            SqlCommand insertFolder =
                new SqlCommand("InsertFolderToFolderIndex", connection);
            insertFolder.CommandType = CommandType.StoredProcedure;

            // Add parameters
            insertFolder.Parameters.Add(CreateParameter("@folderIndexId", SqlDbType.Int, folderIndexId));
            insertFolder.Parameters.Add(CreateParameter("@folderName", SqlDbType.NVarChar, folder.Name));
            insertFolder.Parameters.Add(CreateParameter("@folderKey", SqlDbType.NVarChar, folder.Key));
            insertFolder.Parameters.Add(CreateParameter("@itemCount", SqlDbType.Int, folder.ItemCount));
            insertFolder.Parameters.Add(CreateParameter("@parentFolderId", SqlDbType.Int, parentFolderId));
            insertFolder.Parameters.Add(CreateReturnParameter("@newFolderId", SqlDbType.Int));

            Common.Diagnostics.Trace("InsertFolderToFolderIndex", "SP created and parameterized");

            // Call the SP
            insertFolder.ExecuteNonQuery();
            Common.Diagnostics.Trace("InsertFolderToFolderIndex", "SP ran succesfully");

            // Retrieve out parameter
            int newFolderId = (int)insertFolder.Parameters[5].Value;
            Common.Diagnostics.Trace("InsertFolderToFolderIndex", "Retval retrieved");

            return newFolderId;
        }

        /// <summary>
        /// Retrieves the subfolders of a given folder
        /// </summary>
        /// <param name="folderId">Parent Folder ID</param>
        /// <param name="folderTreeId">Folder tree's ID. This parameter is 
        /// required only for top-level folders</param>
        /// <param name="contentStatId">Content statistic ID</param>
        /// <param name="connection">SQL connection</param>
        /// <returns></returns>
        public static List<Data.Folder> GetSubfolders(
            int folderId, 
            int folderTreeId,
            int contentStatId,
            SqlConnection connection)
        {
            List<Data.Folder> retVal = new List<Data.Folder>();

            // Set up the command object
            SqlCommand getSubfolders =
                new SqlCommand("GetSubfolders", connection);
            getSubfolders.CommandType = CommandType.StoredProcedure;

            // Add parameters
            getSubfolders.Parameters.Add(CreateParameter("@folderId", SqlDbType.Int, folderId));
            getSubfolders.Parameters.Add(CreateParameter("@folderTreeId", SqlDbType.Int, folderTreeId));
            getSubfolders.Parameters.Add(CreateParameter("@contentStatId", SqlDbType.Int, contentStatId));

            Common.Diagnostics.Trace("GetSubfolders", "SP created and parameterized");

            // Call the SP
            SqlDataReader results = getSubfolders.ExecuteReader();
            Common.Diagnostics.Trace("GetSubfolders", "SP ran successfully");

            // Retrieve the field IDs
            int folderIdColumnId = results.GetOrdinal("FolderID");
            int folderNameColumnId = results.GetOrdinal("FolderName");
            int folderKeyColumnId = results.GetOrdinal("FolderKey");
            int indexedColumnId = results.GetOrdinal("Indexed");
            int noteColumnId = results.GetOrdinal("Note");
            int itemCountField = results.GetOrdinal("ItemCount");
            int lastItemModificationField = results.GetOrdinal("LastItemModification");
            int ownerNameField = results.GetOrdinal("OwnerName");
            int actionPlanNameField = results.GetOrdinal("ActionPlanName");
            int ownerEmailField = results.GetOrdinal("OwnerEmail");
            int actionPlanField = results.GetOrdinal("ActionPlanID");
            
            // Convert the results
            while (results.Read())
            {
                Data.Folder folder = new Data.Folder();

                // Getting the mandatory properties
                folder.ID = results.GetInt32(folderIdColumnId);
                folder.Name = results.GetString(folderNameColumnId);
                folder.Key = results.GetString(folderKeyColumnId);
                folder.Indexed = results.GetBoolean(indexedColumnId);
                
                // Getting the optional properties
                folder.ItemCount = results.IsDBNull(itemCountField) ? 
                    (int?)null : results.GetInt32(itemCountField);
                folder.LastModified = results.IsDBNull(lastItemModificationField) ? 
                    DateTime.MinValue : results.GetDateTime(lastItemModificationField);
                folder.OwnerName = results.IsDBNull(ownerNameField) ?
                    null : results.GetString(ownerNameField);
                folder.OwnerEmail = results.IsDBNull(ownerEmailField) ?
                    null : results.GetString(ownerEmailField);
                folder.ActionPlanName = results.IsDBNull(actionPlanNameField) ?
                    null : results.GetString(actionPlanNameField);
                folder.ActionPlan = results.IsDBNull(actionPlanField) ?
                    Data.ActionPlan.MigrateToSharePoint : (Data.ActionPlan)results.GetInt32(actionPlanField);
                folder.Note = results.IsDBNull(noteColumnId) ?
                    null : results.GetString(noteColumnId);
                
                retVal.Add(folder);
            }
            Common.Diagnostics.Trace("GetSubfolders", "Data transformed");

            return retVal;
        }

        /// <summary>
        /// Creates a new folder index record in the database
        /// </summary>
        /// <param name="publicFolderUrl"></param>
        /// <param name="indexName"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        public static int CreateNewFolderIndex(
            string publicFolderUrl, 
            string indexName, 
            SqlConnection connection)
        {
            // Set up the command object
            SqlCommand createNewFolderIndex =
                new SqlCommand("CreateNewFolderIndex", connection);
            createNewFolderIndex.CommandType = CommandType.StoredProcedure;

            // Add parameters
            createNewFolderIndex.Parameters.Add(CreateParameter("@publicFolderUrl", SqlDbType.NVarChar, publicFolderUrl));
            createNewFolderIndex.Parameters.Add(CreateParameter("@indexName", SqlDbType.NVarChar, indexName));
            createNewFolderIndex.Parameters.Add(CreateReturnParameter("@indexId", SqlDbType.Int));

            Common.Diagnostics.Trace("CreateNewFolderIndex", "SP created and parameterized");

            // Call the SP
            createNewFolderIndex.ExecuteNonQuery();
            Common.Diagnostics.Trace("CreateNewFolderIndex", "New folder tree created");

            // Retrieve out parameter
            int indexId = (int)createNewFolderIndex.Parameters[2].Value;
            Common.Diagnostics.Trace("CreateNewFolderIndex", "Retval retrieved");

            return indexId;
        }

        public static int CreateNewContentIndex(
            int folderTreeId,
            string contentIndexName,
            SqlConnection connection)
        {
            // Set up the command object
            SqlCommand createNewContentIndex =
                new SqlCommand("CreateNewContentIndex", connection);
            createNewContentIndex.CommandType = CommandType.StoredProcedure;

            // Add parameters
            createNewContentIndex.Parameters.Add(
                CreateParameter("@folderTreeId", SqlDbType.Int, folderTreeId));
            createNewContentIndex.Parameters.Add(
                CreateParameter("@contentIndexName", SqlDbType.NVarChar, contentIndexName));
            createNewContentIndex.Parameters.Add(
                CreateReturnParameter("@indexId", SqlDbType.Int));

            Common.Diagnostics.Trace("CreateNewContentIndex", "SP created and parameterized");

            // Call the SP
            createNewContentIndex.ExecuteNonQuery();
            Common.Diagnostics.Trace("CreateNewContentIndex", "New content index created");

            // Retrieve out parameter
            int indexId = (int)createNewContentIndex.Parameters[2].Value;
            Common.Diagnostics.Trace("CreateNewContentIndex", "Retval retrieved");

            return indexId;
        }

        /// <summary>
        /// Updates a folder record
        /// </summary>
        /// <param name="folderId"></param>
        /// <param name="folderOwnerName"></param>
        /// <param name="folderOwnerEmail"></param>
        /// <param name="actionPlanId"></param>
        /// <param name="note">Note stored for the folder</param>
        /// <param name="connection"></param>
        public static void UpdateFolder(
            int folderId,
            string folderOwnerName,
            string folderOwnerEmail,
            int actionPlanId,
            string note,
            SqlConnection connection)
        {
            // Set up the command object
            SqlCommand updateFolder =
                new SqlCommand("UpdateFolder", connection);
            updateFolder.CommandType = CommandType.StoredProcedure;

            // Add parameters
            updateFolder.Parameters.Add(
                CreateParameter("@folderId", SqlDbType.Int, folderId));
            updateFolder.Parameters.Add(
                CreateParameter("@ownerName", SqlDbType.NVarChar, folderOwnerName));
            updateFolder.Parameters.Add(
                CreateParameter("@ownerEmail", SqlDbType.NVarChar, folderOwnerEmail));
            updateFolder.Parameters.Add(
                CreateParameter("@actionPlanId", SqlDbType.Int, actionPlanId));
            updateFolder.Parameters.Add(
                CreateParameter("@note", SqlDbType.NVarChar, note));

            Common.Diagnostics.Trace("UpdateFolder", "SP created and parameterized");

            // Call the SP
            updateFolder.ExecuteNonQuery();
            Common.Diagnostics.Trace("UpdateFolder", "SP ran successfully");
        }

        #region Helpers
        /// <summary>
        /// Creates a Sql command parameter
        /// </summary>
        /// <param name="name"></param>
        /// <param name="type"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private static SqlParameter CreateParameter(string name, SqlDbType type, object value)
        {
            SqlParameter result = new SqlParameter(name, type);

            if (value != null)
                result.Value = value;
            else
                result.Value = DBNull.Value;

            return result;
        }

        /// <summary>
        /// Creates a Sql command return parameter
        /// </summary>
        /// <param name="name"></param>
        /// <param name="type"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private static SqlParameter CreateReturnParameter(string name, SqlDbType type)
        {
            SqlParameter result = new SqlParameter(name, type);
            result.Direction = ParameterDirection.ReturnValue;

            return result;
        }

        /// <summary>
        /// Creates a new database connection to the SQL Server
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static SqlConnection CreateConnection(string connectionString)
        {
            // Open the connection
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            Common.Diagnostics.Trace("CreateConnection", "SQL connection opened");

            return connection;
        }
        #endregion
    }
}
